import json
import os
from db_pool_manager import MySQLPoolManager, DatabaseConnection, initialize_db_pools

def create_api_list_table():
    # 初始化连接池
    initialize_db_pools()

    # 创建my_api_list表的SQL
    create_table_sql = """
    CREATE TABLE IF NOT EXISTS my_api_list (
        id INT AUTO_INCREMENT PRIMARY KEY,
        api_key VARCHAR(100) NOT NULL COMMENT 'API标识',
        url VARCHAR(500) NOT NULL COMMENT 'API地址',
        method VARCHAR(10) NOT NULL DEFAULT 'POST' COMMENT '请求方式(POST/GET)',
        parameters TEXT COMMENT '参数配置(JSON格式)',
        status TINYINT(1) DEFAULT 1 COMMENT '状态：1-启用，0-禁用',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY uk_api_key (api_key)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='API配置表';
    """
    
    try:
        # 使用连接池执行创建表操作
        with DatabaseConnection() as cursor:
            cursor.execute(create_table_sql)
            print("成功创建my_api_list表")

            # 读取API文件
            api_file_path = os.path.join(os.path.dirname(__file__), '..', 'execelUtil', 'common', 'api_list')
            with open(api_file_path, 'r', encoding='utf-8') as f:
                content = f.read()
                data = json.loads(content)

                # 转换并插入API配置
                for key, value in data.items():
                    if key.endswith('_url'):
                        param_key = key.replace('_url', '_para_body')
                        api_key = key.replace('_url', '')
                        parameters = json.dumps(data.get(param_key, {}), ensure_ascii=False)

                        # 插入或更新数据
                        insert_sql = """
                        INSERT INTO my_api_list (api_key, url, method, parameters)
                        VALUES (%s, %s, %s, %s)
                        ON DUPLICATE KEY UPDATE
                        url = VALUES(url),
                        method = VALUES(method),
                        parameters = VALUES(parameters)
                        """
                        cursor.execute(insert_sql, (api_key, value, 'POST', parameters))
                        print(f"导入API配置: {api_key}")

            cursor.connection.commit()
            print("成功导入所有API配置数据")

    except Exception as e:
        print(f"创建表或插入数据时出错: {str(e)}")

if __name__ == "__main__":
    create_api_list_table()
